/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;


/**
 *
 * @author tawfeeq
 */
@WebServlet(name = "MyServlet", urlPatterns = {"/MyServlet"})
public class MyServlet extends HttpServlet {
 private String dbUrl = "jdbc:odbc:JavaWeb";
String userName;

  /**Process the HTTP Get request*/
  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException {
    try {
      Connection con = DriverManager.getConnection(dbUrl,"project","project");
      String script = "<script type=\"text/javascript\"> "
        +" var collegeID = new Array(); "
        +" var semesterID= new Array(); "
        +" var collegeSemester = new Array();"
        +" var courseID = new Array(); "
        +" var semsterCourse = new Array();";
    /////////////////////////////////////////////////////
      String sql = "SELECT College_ID" +
        " FROM Colleges";
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(sql);
      int count = 0;
      while(rs.next()){
          script += " collegeID["+count+"] = '"+rs.getString(1)+"';";
          count++;
      }
      
      /////////////////////////////////////////////////////
      sql = "SELECT Semester_ID,College_ID"+
        " FROM Schedule " ;
      Statement s1 = con.createStatement();
      ResultSet rs1 = s1.executeQuery(sql);
      count = 0;
      while(rs1.next()){
          script += " semesterID["+count+"] = '"+rs1.getString(1)+"';";
          script += " collegeSemester["+count+"] = '"+rs1.getString(2)+"';";
          count++;
      }
      /////////////////////////////////////////////////////
      sql = "SELECT Semester_ID,Course_ID "+
        " FROM Schedule " ;
      Statement s2 = con.createStatement();
      ResultSet rs2 = s2.executeQuery(sql);
      count = 0;
      while(rs2.next()){
          script += " courseID["+count+"] = '"+rs2.getString(2)+"';";
          script += " semsterCourse["+count+"] = '"+rs2.getString(1)+"';";
          count++;
      }
      
      script += " function loadColleges(){ "
              +" var count = 0;"
              +" document.getElementById(\"collegeID\").options[0] = new Option(\"- Select College -\",\"\");"
              +" for(i=0;i<cityID.length;i++){"
              +" document.getElementById(\"collegeID\").options[count+1] = new Option(collegeID[i],collegeID[i]);"
              +" count++;}}";
      
      PrintWriter out = response.getWriter();
      out.println(script);
      sendPageHeader(response);
    sendSelectionForm(request, response);
    sendPageFooter(response);
    }
    catch(Exception e){
        
    }
  }
  /**Process the HTTP Post request*/
  public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    userName = request.getParameter("userName");  
    sendPageHeader(response);
     sendSelectionForm(request, response);
    sendPageFooter(response);
  }

  /**
   * Send the HTML page header, including the title
   * and the <BODY> tag
   */
  private void sendPageHeader(HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType("text/html");
    PrintWriter out = response.getWriter();
    out.println("<HTML>");
    out.println("<HEAD>");
    out.println("<TITLE>Quality Assurance</TITLE>");
    out.println("</HEAD>");
    out.println("<BODY onLoad='loadColleges();'>");
    out.println("<CENTER>");
  }
  /**
   * Send the HTML page footer, i.e. the </BODY>
   * and the </HTML>
   */
  private void sendPageFooter(HttpServletResponse response)
    throws ServletException, IOException {
    PrintWriter out = response.getWriter();
    out.println("</CENTER>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<TD><center><A HREF=logout><h3>Logout</h3></A></TD>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<BR/>");
    out.println("<center><p>Copyright &copy; 2011 Hesham Khalil</p>");
    out.println("</BODY>");
    out.println("</HTML>");
  }
  /**Send the form where the user can type in
   * the details for a new user
   */
  private void sendSelectionForm(HttpServletRequest request, HttpServletResponse response)
    throws IOException {
    String id = request.getParameter("id");
    PrintWriter out = response.getWriter();
    out.println("<BR>");
    out.println("<BR>");
    out.println("<h2>Please select the college ID, semester ID, and course ID.</h2>");
    out.println("<TABLE>");
    out.println("<TR>");
    out.println("<TD>College ID</TD>");
    
    
       String sql = "SELECT College_ID" +
        " FROM Colleges";
       
    try {
      Connection con = DriverManager.getConnection(dbUrl,"project","project");
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(sql);
      out.println("<td><select name=\"collegeID\">");
      while (rs.next()) {
        id = rs.getString(1);
        out.println("<option>" + id + "</option>");
      }
        out.println("</select>");
        out.println("</td>");
        out.println("</TR>");
        
        out.println("<tr>");
        out.println("</tr>");
        
      s.close();
      con.close();
    }
    catch (SQLException e) {
    }
    catch (Exception e) {
    }
    out.println("<BR>");
    out.println("<BR/>");
    out.println("<TR>");
    out.println("<TD>Semester ID</TD>");
    sql = 
        "SELECT Semester_ID"+
        " FROM Semesters" ;
       
    try {
     Connection con = DriverManager.getConnection(dbUrl,"project","project");
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(sql);
     
      out.println("<td><select name=semesterID>");
      while (rs.next()) {
        id = rs.getString(1);
        out.println("<option>" + id + "</option>");
      }
        out.println("</select>");
        out.println("</td>");
        out.println("</TR>");
      s.close();
      con.close();
    }
    catch (SQLException e) {
    }
    catch (Exception e) {
    }  
       out.println("<tr>");
        out.println("</tr>");
       out.println("<TR>");
       out.println("<TD>Course ID</TD>");
    sql = 
        "SELECT Course_ID"+
        " FROM Sechdule";
    
    //WHERE College_ID='"+collegeID+"' "+
       // "AND Semester_ID="+semesterID;
       // "WHERE COLLEGE_ID="+collegeID;
       
    try {
     Connection con = DriverManager.getConnection(dbUrl,"project","project");
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(sql);
     
      out.println("<td><select name=\"courseID\">");
      while (rs.next()) {
        id = rs.getString(1);
        out.println("<option>" + id + "</option>");
      }
        out.println("</select>");
        out.println("</td>");
        out.println("</TR>");
      s.close();
      con.close();
    }
    catch (SQLException e) {
    }
    catch (Exception e) {
    }
        out.println("<tr>");
        out.println("<br>");
        out.println("</tr>");
        out.println("<TR>"); 
        out.println("<td><input type=submit  value=\"Course Spec\"></td>");
        out.println("<td><input type=submit  value=\"Course Report\"></td>");
        out.println("</TR>");
        out.println("</TABLE>");
        out.println("</FORM>");
      }
  }
